package cmd

import (
	"database/sql"
	"fmt"
	"gitee.com/cdsailyj/go-vpn/common/tool"
	"gitee.com/cdsailyj/go-vpn/initialize"
	"github.com/xuri/excelize/v2"
	"strconv"
)

type TableCmd struct{}

// SELECT
//
//	a.COLUMN_NAME 字段,
//	a.DATA_TYPE 类型,
//	a.DATA_LENGTH 长度,
//	a.DATA_PRECISION  精度,
//	a.NULLABLE 可空,
//	a.DATA_DEFAULT 默认值,
//	b.COMMENTS 注释
//
// FROM
//
//	ALL_TAB_COLUMNS a
//	LEFT JOIN ALL_COL_COMMENTS b ON a.TABLE_NAME = b.TABLE_NAME
//	AND a.COLUMN_NAME = b.COLUMN_NAME
//
// WHERE
//
//	a.TABLE_NAME = 'SYS_OFFICE';
type TableComment struct {
	TABLE_NAME string         `db:"TABLE_NAME" json:"TABLE_NAME" form:"TABLE_NAME"`
	COMMENTS   sql.NullString `db:"COMMENTS" json:"COMMENTS" form:"COMMENTS"`
}

type Column struct {
	COLUMN_NAME    string         `db:"COLUMN_NAME" json:"COLUMN_NAME" form:"COLUMN_NAME"`
	DATA_TYPE      string         `db:"DATA_TYPE" json:"DATA_TYPE" form:"DATA_TYPE"`
	DATA_LENGTH    sql.NullString `db:"DATA_LENGTH" json:"DATA_LENGTH" form:"DATA_LENGTH"`
	DATA_PRECISION sql.NullString `db:"DATA_PRECISION" json:"DATA_PRECISION" form:"DATA_PRECISION"`
	NULLABLE       sql.NullString `db:"NULLABLE" json:"NULLABLE" form:"NULLABLE"`
	DATA_DEFAULT   sql.NullString `db:"DATA_DEFAULT" json:"DATA_DEFAULT" form:"DATA_DEFAULT"`
	COMMENTS       sql.NullString `db:"COMMENTS" json:"COMMENTS" form:"COMMENTS"`
}

type Table struct {
	TABLE_NAME string `db:"TABLE_NAME" json:"TABLE_NAME" form:"TABLE_NAME"`
}

func (t *TableCmd) Export() {
	db, _ := initialize.GetYwkDB()
	//db, _ := initialize.GetXtkDB()
	//db, _ := initialize.GetLznlptDB()

	var tableComments []TableComment
	sql1 := "SELECT COMMENTS, TABLE_NAME FROM user_tab_comments WHERE TABLE_NAME IN('RECLAIM_SOIL','RECLAIM_UNDER_WATER','RECLAIM_SURFACE_WATER','VOCS_OPEN_POOL','VOCS_STORAGE_TANK','VOCS_FACILITY_RECORDS','VOCS_LDAR_SUMMARY','VOCS_LDAR_YEAR_SUMMARY')"
	db.Select(&tableComments, sql1)

	tableCommentsMap := make(map[string]string)
	for _, comment := range tableComments {
		tableCommentsMap[comment.TABLE_NAME] = comment.COMMENTS.String
	}

	var tables []Table
	tableSQL := "SELECT DISTINCT TABLE_NAME FROM user_tables WHERE TABLE_NAME IN('RECLAIM_SOIL','RECLAIM_UNDER_WATER','RECLAIM_SURFACE_WATER','VOCS_OPEN_POOL','VOCS_STORAGE_TANK','VOCS_FACILITY_RECORDS','VOCS_LDAR_SUMMARY','VOCS_LDAR_YEAR_SUMMARY')"
	db.Select(&tables, tableSQL)

	headers := []tool.ExcelSheetHeader{
		{Key: "COMMENTS", Label: "数据项名称", Width: 50.0},
		{Key: "COLUMN_NAME", Label: "拼音代码", Width: 30.0},
		{Key: "DATA_TYPE", Label: "数据类型", Width: 30.0},
		{Key: "DATA_LENGTH", Label: "宽度"},
		{Key: "DATA_PRECISION", Label: "小数位数"},
		{Key: "NULLABLE", Label: "可空"},
		{Key: "DATA_DEFAULT", Label: "默认值"},
	}

	detailStyle := &excelize.Style{
		Font: &excelize.Font{Color: "#f96868", Bold: true},
	}

	var allData []tool.ExcelCol[Column]
	for index, table := range tables {
		var results []Column

		sqlString := fmt.Sprintf(`SELECT
  a.COLUMN_NAME,
  a.DATA_TYPE,
  a.DATA_LENGTH,
  a.DATA_PRECISION,
  a.NULLABLE,
  a.DATA_DEFAULT,
  b.COMMENTS
FROM
  ALL_TAB_COLUMNS a
  LEFT JOIN ALL_COL_COMMENTS b ON a.TABLE_NAME = b.TABLE_NAME 
  AND a.COLUMN_NAME = b.COLUMN_NAME 
WHERE
  a.TABLE_NAME = :1`)
		err := db.Select(&results, sqlString, table.TABLE_NAME)
		if err != nil {
			fmt.Printf("错误：%#v \n", err)
			break
		}
		if results == nil {
			break
		}

		sp := []tool.ExcelCol[Column]{
			{Value: Column{COLUMN_NAME: ""}},
			{Value: Column{COLUMN_NAME: ""}},
			{Value: Column{COLUMN_NAME: tableCommentsMap[table.TABLE_NAME], COMMENTS: sql.NullString{Valid: true, String: strconv.Itoa(index + 1)}}},
			{Value: Column{COLUMN_NAME: fmt.Sprintf("%s [%s]", table.TABLE_NAME, tableCommentsMap[table.TABLE_NAME])}, Style: detailStyle},
			{Value: Column{COLUMN_NAME: ""}},
		}

		allData = append(allData, sp...)
		for _, v := range results {
			allData = append(allData, tool.ExcelCol[Column]{Value: v})
		}
	}

	sheet := []tool.ExcelSheet[Column]{
		{Name: "表结构", Headers: headers, Data: allData},
	}
	excel, _ := tool.ExportExcel(sheet)

	err := excel.SaveAs(fmt.Sprintf("D:\\download\\excel\\%s.xlsx", "env"))
	if err != nil {
		fmt.Printf("输出结果: %#v \n", err)
	}

}
